﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfApp.Models;

namespace WpfApp.Services
{
    internal interface IUserService
    {
        // 注册，如果账号名已存在则返回错误,
        // 如果成功则自动创建“历史查询”，“我的生词本”两个单词本
        bool Register(string username, string password);
        bool Login(string username, string password);

        /*对于WordBook的操作，实现都需要两步*/
        // 根据用户名查找返回单词本列表，按创建时间排序:，先获得WordbookIDs
        List<Tuple<int, string>> GetUserWordbookIDs(string username);
        
        // 创建某个新的单词本
        //先实现创建wordbook
        public int CreateWordbookInDatabase(string username, string title);
        // 删除单词本
        bool DeleteWordBook(string username, string title);
        // 单词本重命名
        bool RenameWordBook(string username, string oldTitle, string newTitle);

        //单词
        long GetUserId(string username);
        long GetWordbookId(string username, string title);
    }

    internal class UserService: IUserService
    {
        private string UserDatasetFilePath;
        public UserService(string userDatabaseFilePath)
        {
            UserDatasetFilePath = userDatabaseFilePath;
        }
        public bool Login(string username, string password)
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                var query = "SELECT COUNT(1) FROM User WHERE Name = @Username AND Password = @Password";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    command.Parameters.AddWithValue("@Password", password);
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return count > 0;
                }
            }
        }
        public bool Register(string username, string password)
        {
            if (CheckUserExists(username))
            {
                return false;
            }

            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 插入用户信息
                        var insertUserQuery = "INSERT INTO User (Name, Password, CreatedTime) VALUES (@Username, @Password, @CreatedTime)";
                        using (var command = new SqliteCommand(insertUserQuery, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Username", username);
                            command.Parameters.AddWithValue("@Password", password);
                            command.Parameters.AddWithValue("@CreatedTime", DateTime.Now);
                            command.ExecuteNonQuery();
                        }
                        DatabaseHelper.Log($"用户名：{username}，password:{password}", "RegisterLog.txt");

                        // 获取新插入的用户ID
                        var userIdQuery = "SELECT last_insert_rowid()";
                        long userId;
                        using (var command = new SqliteCommand(userIdQuery, connection, transaction))
                        {
                            var result= command.ExecuteScalar();
                            userId = result as long? ?? 0;
                        }

                        // 创建默认的两个单词本
                        AddWordBookInternal(connection, transaction, userId, "历史查询");
                        AddWordBookInternal(connection, transaction, userId, "我的生词本");

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        private void AddWordBookInternal(SqliteConnection connection, SqliteTransaction transaction, long userId, string title)
        {
            //分三步，插入wordbook表，创建用户单词本关系
            var insertWordbookQuery = "INSERT INTO Wordbook (Name, CreatedTime) VALUES (@Title, @CreatedTime)";
            using (var command = new SqliteCommand(insertWordbookQuery, connection, transaction))
            {
                command.Parameters.AddWithValue("@Title", title);
                command.Parameters.AddWithValue("@CreatedTime", DateTime.Now);
                command.ExecuteNonQuery();
            }

            // 获取新插入的WordbookID
            var wordbookIdQuery = "SELECT last_insert_rowid()";
            long wordbookId;
            using (var command = new SqliteCommand(wordbookIdQuery, connection, transaction))
            {
                var result=command.ExecuteScalar();
                wordbookId = result as long? ?? 0;
            }

            // 将用户和Wordbook关联起来
            var insertUserWordbookQuery = "INSERT INTO User_Wordbook (UserID, WordbookID) VALUES (@UserID, @WordbookID)";
            using (var command = new SqliteCommand(insertUserWordbookQuery, connection, transaction))
            {
                command.Parameters.AddWithValue("@UserID", userId);
                command.Parameters.AddWithValue("@WordbookID", wordbookId);
                command.ExecuteNonQuery();
            }
        }

        public bool CheckUserExists(string username)
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                var query = "SELECT COUNT(1) FROM User WHERE Name = @Username";

                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    var count = Convert.ToInt32(command.ExecuteScalar());
                    return count > 0;
                }
            }
        }

        //单词本名字读取操作操作,需要展示title，返回wordbookid来查询有多少单词
        public List<Tuple<int, string>> GetUserWordbookIDs(string username)
        {
            var wordbookIds = new List<Tuple<int, string>>();
            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                var query = @"
                    SELECT wb.WordbookID,wb.Name
                    FROM User u
                    INNER JOIN User_Wordbook uw ON u.UserID = uw.UserID
                    INNER JOIN Wordbook wb ON uw.WordbookID = wb.WordbookID
                    WHERE u.Name = @Username
                    ";
                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            wordbookIds.Add(new Tuple<int, string> (
                                reader.GetInt32(0),
                                reader.GetString(1)
                                    ));
                        }
                    }
                }
            }
            return wordbookIds;
        }
        public int CreateWordbookInDatabase(string username, string title)
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath)) 
            {
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var insertWordbookQuery = @"
                        INSERT INTO Wordbook (Name)
                        VALUES (@Title);
                        SELECT last_insert_rowid();
                        ";//不用插入时间，会自动生成
                        int wordbookId;
                        using (var command = new SqliteCommand(insertWordbookQuery, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Title", title);
                            wordbookId = Convert.ToInt32(command.ExecuteScalar());
                        }

                        // 获取用户的UserID
                        var getUserIdQuery = @"
                        SELECT UserID
                        FROM User
                        WHERE Name = @Username
                         ";
                        int userId;
                        using (var command = new SqliteCommand(getUserIdQuery, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Username", username);
                            userId = Convert.ToInt32(command.ExecuteScalar());
                        }

                        // 在User_Wordbook中关联用户和新的Wordbook
                        var insertUserWordbookQuery = @"
                            INSERT INTO User_Wordbook (UserID, WordbookID)
                            VALUES (@UserID, @WordbookID)
                        ";
                        using (var command = new SqliteCommand(insertUserWordbookQuery, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@UserID", userId);
                            command.Parameters.AddWithValue("@WordbookID", wordbookId);
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        return wordbookId;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw; // 抛出异常，通知调用者操作失败
                    }
                }
            }
        }
        // 获取 LearningProgress 数据的方法
        public DataTable GetLearningProgressData(long userId, int? scoreThreshold, bool? learned)
        {
            string query = @"
            SELECT lp.WordID, lp.CorrectCount
            FROM LearningProgress lp
            WHERE lp.UserID = @UserID";

            if (scoreThreshold.HasValue)
            {
                query += learned.HasValue && learned.Value ? " AND lp.CorrectCount >= @ScoreThreshold"
                                                           : " AND lp.CorrectCount < @ScoreThreshold";
            }

            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@UserID", userId);
                    if (scoreThreshold.HasValue)
                    {
                        command.Parameters.AddWithValue("@ScoreThreshold", scoreThreshold.Value);
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
        }

        //单词查询操作(辅助作用)
        public long GetUserId(string username)
        {
            using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
            {
                connection.Open();
                var query = "SELECT UserID FROM User WHERE Name = @Username";
                using (var command = new SqliteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);
                    var result = command.ExecuteScalar();
                    if (result == null)
                    {
                        throw new Exception($"GetUserId失败，请先创建用户{username}");
                    }
                    return (long)result;//如果不debug，直接可以使用 return retult as long? ?? 0; 
                }
            }
        }
        public long GetWordbookId(string username, string title)
        {
            try
            {
                using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
                {
                    connection.Open();

                    // 获取 UserID 并同时验证用户名是否存在
                    var userIdQuery = "SELECT UserID FROM User WHERE Name = @Username";
                    long userId;
                    using (var userIdCommand = new SqliteCommand(userIdQuery, connection))
                    {
                        userIdCommand.Parameters.AddWithValue("@Username", username);
                        var result = userIdCommand.ExecuteScalar();
                        if (result == null)
                        {
                            throw new Exception($"GetWordbookId失败，请先创建用户{username}");
                        }
                        userId = (long)result;
                    }

                    // 获取 WordbookID 并同时验证用户名和单词本的对应关系
                    var wordbookIdQuery = @"
                    SELECT wb.WordbookID
                    FROM Wordbook wb
                    JOIN User_Wordbook uw ON wb.WordbookID = uw.WordbookID
                    WHERE wb.Name = @Title AND uw.UserID = @UserID";
                    using (var command = new SqliteCommand(wordbookIdQuery, connection))
                    {
                        command.Parameters.AddWithValue("@Title", title);
                        command.Parameters.AddWithValue("@UserID", userId);
                        var result = command.ExecuteScalar();
                        if (result == null)
                        {
                            throw new Exception($"GetWordbookId失败，请先为{username}创建名为{title}的wordbook.");
                        }
                        return (long)result;
                    }
                }
            }
            catch 
            {
                return -1;
                throw;
            }
        }
        public bool DeleteWordBook(string username, string title)
        {
            try
            {
                // 获取 UserID 和 WordbookID
                var userId = GetUserId(username);
                var wordbookId = GetWordbookId(username, title);

                using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            // 删除 User_Wordbook 中的关联记录
                            var deleteUserWordbookQuery = @"
                            DELETE FROM User_Wordbook 
                            WHERE UserID = @UserID AND WordbookID = @WordbookID";
                            using (var command = new SqliteCommand(deleteUserWordbookQuery, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@UserID", userId);
                                command.Parameters.AddWithValue("@WordbookID", wordbookId);
                                command.ExecuteNonQuery();
                            }
                            // 删除 Wordbook 表中的记录
                            var deleteWordbookQuery = "DELETE FROM Wordbook WHERE WordbookID = @WordbookID";
                            using (var command = new SqliteCommand(deleteWordbookQuery, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@WordbookID", wordbookId);
                                command.ExecuteNonQuery();
                            }

                            transaction.Commit();
                            return true;
                        }
                        catch
                        {
                            transaction.Rollback();
                            return false;
                        }
                    }
                }
            }
            catch
            {
                return false;
            }
        }
        public bool RenameWordBook(string username, string oldTitle, string newTitle)
        {
            try
            {
                // 获取 UserID 和 WordbookID
                var userId = GetUserId(username);
                var wordbookId = GetWordbookId(username, oldTitle);

                using (var connection = DatabaseHelper.GetDatabaseConnection(UserDatasetFilePath))
                {
                    connection.Open();

                    // 更新 Wordbook 表中的名称
                    var updateWordbookQuery = @"
                UPDATE Wordbook
                SET Name = @NewTitle
                WHERE WordbookID = @WordbookID AND EXISTS (
                    SELECT 1 FROM User_Wordbook WHERE UserID = @UserID AND WordbookID = @WordbookID
                )";

                    using (var command = new SqliteCommand(updateWordbookQuery, connection))
                    {
                        command.Parameters.AddWithValue("@NewTitle", newTitle);
                        command.Parameters.AddWithValue("@WordbookID", wordbookId);
                        command.Parameters.AddWithValue("@UserID", userId);

                        var rowsAffected = command.ExecuteNonQuery();
                        return rowsAffected > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
        }


    }
}
